package model;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class PlayerDao {

	private String sqlUser = "SELECT * FROM APP.users";
	private String sqlUserGet = "SELECT * FROM APP.users WHERE username=? AND password = ?";
	private String sqlUserBalance = "SELECT balance FROM APP.users WHERE username=?";
	private String sqlUserdelete = "DELETE FROM APP.users where username = ?";
	private String sqlUsercreate = "INSERT INTO APP.users (username, password, balance)  VALUES(?, ?, ?)";
	private String sqlUserSetBalance = "UPDATE APP.users SET balance=? WHERE username = ?";

	private Connection con = null;
	private PreparedStatement psUser = null;
	private PreparedStatement psUserGet = null;
	private PreparedStatement psCreate = null;
	private PreparedStatement psDelete = null;
	private PreparedStatement psBalance = null;
	private PreparedStatement psSetBalance = null;

	public PlayerDao() {
		DBmanager myDb = DBmanager.getInstance();
		con = myDb.getConnection();
		createTables();
		try {
			this.psUser = con.prepareStatement(sqlUser);
			this.psDelete = con.prepareStatement(sqlUserdelete);
			this.psCreate = con.prepareStatement(sqlUsercreate);
			this.psUserGet = con.prepareStatement(sqlUserGet);
			this.psBalance = con.prepareStatement(sqlUserBalance);
			this.psSetBalance = con.prepareStatement(sqlUserSetBalance);
		} catch (SQLException se) {
			printSQLException(se);
		}
	}
	
	public void createTables() {

		try {
			// get the table listing
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet rs = dbmd.getTables(null, null, null, new String[] { "TABLE" });
			ArrayList<String> tableList = new ArrayList<String>();
			while (rs.next()) {
				tableList.add(rs.getString("TABLE_NAME"));
			}
			// check if the table does not already exists and then create them
			// if needed
			if (!tableList.contains("USERS")) {
				Statement stat = con.createStatement();
				stat.execute("CREATE TABLE APP.visitdata ("
						  + "id int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
						  + "ip varchar(15) NOT NULL,"
						  + "browser varchar(10) NOT NULL,"
						  + "lang varchar(10) NOT NULL,"
						  + "page varchar(50) NOT NULL,"
						  + "PRIMARY KEY  (id))");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public int getChips(String username) {

		int balance = 0;

		try {
			psBalance.clearParameters();
			psBalance.setString(1, username);
			ResultSet rs = psBalance.executeQuery();

			while (rs.next()) {
				return rs.getInt("balance");
			}

			rs.close();

		} catch (SQLException se) {
			printSQLException(se);
		}

		return balance;
	}
	
	public void setChips(String username, int chips) {
		try {
			psSetBalance.clearParameters();
			psSetBalance.setInt(1, chips);
			psSetBalance.setString(2, username);
			
			psSetBalance.executeUpdate();
			
		} catch(SQLException se) {
			printSQLException(se);
		}
	}
	
	public boolean existingUser(String username) {
		try {
			boolean bestaat = false;
			
			if(bestaat) {
				return true;
			}
			return false;
			
		} catch (Exception e) { return true; }
	}

	public boolean checkUser(String username, String password) {
		try {
			psUserGet.clearParameters();
			psUserGet.setString(1, username);
			psUserGet.setString(2, password);

			ResultSet rs = psUserGet.executeQuery();

			while (rs.next()) {
				rs.close();
				return true;
			}

			rs.close();
		} catch (Exception ex) {
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		}
		return false;
	}
	
	public boolean newUser(String username, String password) {
		try {

			psCreate.clearParameters();

			psCreate.setString(1, username);
			psCreate.setString(2, password);
			psCreate.setInt(3, 1000);
			
			int resultaat = psCreate.executeUpdate();
			if (resultaat == 0) {
				System.out.println("Niks gedaan..");
				return false;
			}
			return true;
		} catch (SQLException se) {
			printSQLException(se);
		}
		return false;
	}

	private static void printSQLException(SQLException se) {
		while (se != null) {
			System.out.print("SQLException: State:   " + se.getSQLState());
			System.out.println("Severity: " + se.getErrorCode());
			System.out.println(se.getMessage());

			se = se.getNextException();
		}
	}

}
